PGCon2016 - 20180510
PGCon 2016
The PostgreSQL Conference
| Speakers | |
|---|---|
|
Tatsuro Yamada |
| Schedule | |
|---|---|
| Day | Talks - Day 2 - 2016-05-20 |
| Room | DMS 1110 |
| Start time | 15:00 |
| Duration | 00:45 |
| Info | |
| ID | 956 |
| Event type | Lecture |
| Track | Case Studies |
| Language used for presentation | English |
A Challenge of Huge Billing System Migration
Migration project of a mission-critical database system to PostgreSQL will be presented. The target is the billing system of a large Japanese telecommunication company. The system handles quite a large amount of data and performs both transactional and analytic operations. The most difficult challenges of the project were to stabilize the batch program performances. In this talk, I will describe the issues that occurred in the projects and how we approached them. We prepared a tool affecting PostgreSQL planner, which solved the toughest problems of the project. This talk will cover it as well.
NTT, one of the world top 10 telecom companies, has thousands of database systems in daily operation. For the purpose of efficient and cost effective operations, it migrates roughly 100 of them from commercial DBMS to PostgreSQL each year. Last year, we undertook a giant project; the target was the billing system of NTT Communications, the subsidiary for the international and long-distance calls. This system engages the billing operations of nearly 100 services for some millions of institutional customers. Furthermore, the company has a lot of retail customers. and total sales amount is 1.2 trillion yen (roughly 10 billion dollars). The system handles at least over 30% of it. It is really the heart of the corporation. In addition to regular transactional operations, it also handles various analytic tasks.
The most difficult challenges of the project were in over night batch executions. There are roughly 5,000 batch programs. In early trial operations, some of them showed very unstable performance. From time to time, the execution time swayed from minutes to a few days! We analyzed the problems and found the causes were related to query planning. We coped with these problems by performing careful re-collection of table statistics, adjustments of queries and adjustments of application programs. The talk will cover details of them. Most of the problems were solved by those tactics, but there were still a few that were not. Our ultimate weapon was a tool called "pg_hint_plan". All the remaining problems were solved by this tool, and we talk will also discuss this "forbidden fruit".